package com.sl.au.repository;

import java.util.List;

//import org.activiti.engine.impl.Page;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Chance;

@Transactional(readOnly = true)
public interface ChanceRepository extends JpaRepository<Chance, String> {

	/**
	 * 获取该用户下搜索数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select c from Chance c where c.user.id =?1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%' "
			+ "or c.projectState like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%')")
	Page<Chance> findSearchChance(String userId, String key, Pageable pageable);

	/**
	 * 查出key条件下数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query(" select count(*) from Chance c where c.user.id = ?1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%' "
			+ "or c.projectState like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%')")
	Long getChanceCount(String userId, String key);

	/**
	 * 获取客户相关的SaleChance
	 * 
	 * @param customerId
	 * @return
	 */

	@Query(" select s from Chance s where s.customerId =?1 and (s.name like '%'+?2+'%' "
			+ "or s.source like '%'+?2+'%' or s.sourceUnit like '%'+?2+'%' "
			+ "or s.orderUnit like '%'+?2+'%' or s.orderPerson like '%'+?2+'%'" + "or s.user.empLogin.userName like '%'+?2+'%')")
	Page<Chance> findCustomerChance(String customerId, String key,Pageable pageable);

	@Query(" select count(*) from Chance s where s.customerId =?1 and (s.name like '%'+?2+'%' "
			+ "or s.source like '%'+?2+'%' or s.sourceUnit like '%'+?2+'%' "
			+ "or s.orderUnit like '%'+?2+'%' or s.orderPerson like '%'+?2+'%'" + "or s.user.empLogin.userName like '%'+?2+'%')")
	Long getCustomerChanceCount(String customerId,String key);
	


	///////// 信息查询菜单数据///////////
	/**
	 * 获取所有搜索数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select c from Chance c where  c.isSubmit = 1 and (c.name like '%'+?1+'%' "
			+ "or c.source like '%'+?1+'%' or c.sourceUnit like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.orderPerson like '%'+?1+'%'" + "or c.user.empLogin.userName like '%'+?1+'%')")
	Page<Chance> findQueryChanceAll(String key, Pageable pageable);

	/**
	 * 查出key条件下数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query(" select count(*) from Chance c where  c.isSubmit = 1 and (c.name like '%'+?1+'%' "
			+ "or c.source like '%'+?1+'%' or c.sourceUnit like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.orderPerson like '%'+?1+'%'" + "or c.user.empLogin.userName like '%'+?1+'%')")
	Long getQueryAllCount(String key);

	/**
	 * 获取处长搜索数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select c from Chance c where  c.user.saleArea.superior = ?1 and c.isSubmit = 1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceDeliveryTemp c where c.receiveSuperior =?1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	Page<Chance> findQueryChanceDelivery(String superior, String key, Pageable pageable);

	/**
	 * 查出处长key条件下数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query("select c from Chance c where  c.user.saleArea.superior = ?1 and c.isSubmit = 1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceDeliveryTemp c where c.receiveSuperior =?1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	List<Chance> getQueryDeliveryCount(String superior, String key);

	/**
	 * 获取经理搜索数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select c from Chance c where  c.user.saleArea.id = ?1 and c.isSubmit = 1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceDelivery c where c.saleArea.id =?1 and c.isReceived =1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	Page<Chance> findQueryChanceManager(String areaId, String key, Pageable pageable);

	/**
	 * 查出经理key条件下数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query("select c from Chance c where  c.user.saleArea.id = ?1 and c.isSubmit = 1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceDelivery c where c.saleArea.id =?1 and c.isReceived =1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	List<Chance> getQueryManagerCount(String areaId, String key);
	/**
	 * 获取当前人员搜索数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select c from Chance c where  c.user.id = ?1 and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceManagerDelivery c where c.user.id =?1 and c.isReceived =1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	Page<Chance> findQueryChanceUser(String userId, String key, Pageable pageable);

	/**
	 * 查出当前人员key条件下数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query("select c from Chance c where  c.user.id = ?1  and (c.name like '%'+?2+'%' "
			+ "or c.source like '%'+?2+'%' or c.sourceUnit like '%'+?2+'%' "
			+ "or c.orderUnit like '%'+?2+'%' or c.orderPerson like '%'+?2+'%'" + "or c.user.empLogin.userName like '%'+?2+'%') "
			+ "UNION "
			+ "select c.saleChance from ChanceManagerDelivery c where c.user.id =?1 and c.isReceived =1 and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.user.empLogin.userName like '%'+?2+'%')")
	List<Chance> getQueryUserCount(String userId, String key);
}
